package com.pzhu.dao.impl;

import com.pzhu.dao.BookDao;
import com.pzhu.pojo.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {
    @Override
    public List<Book> selectAllBooks() {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<Book> list = new ArrayList<Book>();

        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp?useUnicode=true&characterEncoding=utf8", "root", "0714");

            String sql = "select * from t_book";

            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            while (rs.next()) {
                list.add(new Book(rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4), rs.getString(5)));
//				Book book=new Book();
//				book.setId(rs.getInt("id"));
//				book.setName(rs.getString("name"));
//				book.setPrice(rs.getDouble("price"));
//				book.setAuthor(rs.getString("author"));
//				book.setPublisher(rs.getString("publisher"));
//				list.add(book);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public int insertBook(Book book) {

        Connection conn = null;
        PreparedStatement ps = null;

        int index = 0;

        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp?useUnicode=true&characterEncoding=utf8", "root", "0714");

            String sql = "insert into t_book values(default,?,?,?,?)";

            ps = conn.prepareStatement(sql);

            ps.setObject(1, book.getName());
            ps.setObject(2, book.getPrice());
            ps.setObject(3, book.getAuthor());
            ps.setObject(4, book.getPublisher());

            index = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return index;
    }

    @Override
    public void delBook(int id) {
        Connection conn = null;
        PreparedStatement ps = null;

        int index = 0;

        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp?useUnicode=true&characterEncoding=utf8", "root", "0714");

            String sql = "delete from t_book where id = ?";

            ps = conn.prepareStatement(sql);

            ps.setObject(1, id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<Book> selectBook(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<Book> list = new ArrayList<Book>();

        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp?useUnicode=true&characterEncoding=utf8", "root", "0714");

            String sql = "select * from t_book where id = ?";

            ps = conn.prepareStatement(sql);

            ps.setObject(1, id);
            rs = ps.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString(2));
                list.add(new Book(rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4), rs.getString(5)));
//				Book book=new Book();
//				book.setId(rs.getInt("id"));
//				book.setName(rs.getString("name"));
//				book.setPrice(rs.getDouble("price"));
//				book.setAuthor(rs.getString("author"));
//				book.setPublisher(rs.getString("publisher"));
//				list.add(book);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public int updateBook(Book book) {
        Connection conn = null;
        PreparedStatement ps = null;

        int index = 0;

        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp?useUnicode=true&characterEncoding=utf8", "root", "0714");

            String sql = "update t_book set name = ?,price = ?,author = ?,publisher = ? where id = ?";

            ps = conn.prepareStatement(sql);

            ps.setObject(1, book.getName());
            ps.setObject(2, book.getPrice());
            ps.setObject(3, book.getAuthor());
            ps.setObject(4, book.getPublisher());
			ps.setObject(5, book.getId());
            index = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return index;
    }
}
